/**
 * OWASP GoatDroid Project
 * 
 * This file is part of the Open Web Application Security Project (OWASP)
 * GoatDroid project. For details, please see
 * https://www.owasp.org/index.php/Projects/OWASP_GoatDroid_Project
 *
 * Copyright (c) 2011 - The OWASP Foundation
 * 
 * GoatDroid is published by OWASP under the GPLv3 license. You should read and accept the
 * LICENSE before you use, modify, and/or redistribute this software.
 * 
 * @author Jack Mannino, nVisium Security (https://www.nvisiumsecurity.com)
 * @created 2011
 */
package org.owasp.goatdroid.fourgoats.webservice.rewards;

import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import org.owasp.goatdroid.fourgoats.webservice.Constants;
import org.owasp.goatdroid.fourgoats.webservice.base.BaseDAO;
import com.mysql.jdbc.Connection;

public class RewardsDAO extends BaseDAO {

	public RewardsDAO() throws InstantiationException, IllegalAccessException,
			ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		conn = (Connection) DriverManager
				.getConnection(Constants.DB_CONNECTION_STRING);
	}

	public HashMap<String, String> getAllRewards() throws SQLException {

		String sql = "select rewards.rewardName, rewards.rewardDescription, "
				+ "venues.venueName, rewards.checkinsRequired, venues.latitude, "
				+ "venues.longitude from rewards inner join venues on rewards.venueID = "
				+ "venues.venueID";
		PreparedStatement selectStatement = (PreparedStatement) conn
				.prepareCall(sql);
		ResultSet rs = selectStatement.executeQuery();
		HashMap<String, String> rewards = new HashMap<String, String>();
		int count = 0;
		while (rs.next()) {
			rewards.put("rewardName" + count, rs.getString("rewardName"));
			rewards.put("rewardDescription" + count,
					rs.getString("rewardDescription"));
			rewards.put("venueName" + count, rs.getString("venueName"));
			rewards.put("checkinsRequired" + count,
					Integer.toString(rs.getInt("checkinsRequired")));
			rewards.put("latitude" + count, rs.getString("latitude"));
			rewards.put("longitude" + count, rs.getString("longitude"));
			count++;
		}
		return rewards;
	}

	public HashMap<String, String> getEarnedRewards(String userID)
			throws SQLException {

		String sql = "select rewards.rewardName, rewards.rewardDescription, earned_rewards.timeEarned "
				+ "from earned_rewards inner join rewards on rewards.rewardID = earned_rewards.rewardID "
				+ "where earned_rewards.userID = ?";
		PreparedStatement selectStatement = (PreparedStatement) conn
				.prepareCall(sql);
		selectStatement.setString(1, userID);
		ResultSet rs = selectStatement.executeQuery();
		HashMap<String, String> rewards = new HashMap<String, String>();
		int count = 0;
		while (rs.next()) {
			rewards.put("rewardName" + count, rs.getString("rewardName"));
			rewards.put("rewardDescription" + count,
					rs.getString("rewardDescription"));
			rewards.put("timeEarned" + count, rs.getString("timeEarned"));
			count++;
		}
		return rewards;
	}

	public void addNewReward(String rewardID, String rewardName,
			String rewardDescription, String venueID, int checkinsRequired)
			throws SQLException {

		String sql = "insert into rewards (rewardID, rewardName, rewardDescription, venueID, checkinsRequired) values (?,?,?,?,?)";
		PreparedStatement insertStatement = (PreparedStatement) conn
				.prepareCall(sql);
		insertStatement.setString(1, rewardID);
		insertStatement.setString(2, rewardName);
		insertStatement.setString(3, rewardDescription);
		insertStatement.setString(4, venueID);
		insertStatement.setInt(5, checkinsRequired);
		insertStatement.executeUpdate();
	}

	public boolean doesVenueExist(String venueID) throws SQLException {

		String sql = "select venueID from venues where venueID = ?";
		PreparedStatement selectStatement = (PreparedStatement) conn
				.prepareCall(sql);
		selectStatement.setString(1, venueID);
		ResultSet rs = selectStatement.executeQuery();
		if (rs.next())
			return true;
		else
			return false;
	}
}
